import pandas as pd
import json
from typing import List
import copy
from itertools import zip_longest

# 最终xlsx文件的字段
word_header = ["word_id", "phrase", "frequency_name", "chinese", "example", "translation", "notes"]


def write_data_to_xlsx(data: List, columns, file_name):
    """
    写入文件
    :param data:
    :param columns:
    :param file_name:
    :return:
    """
    pd.DataFrame(data, columns=columns).to_excel(r'./parse_collocation/{0}.xlsx'.format(file_name),
                                                 sheet_name='Sheet1',
                                                 index=False,
                                                 header=True
                                                 )


def parse_collocation(gy_fixed_collocation):
    collocation_data = []
    for collocation in gy_fixed_collocation:
        fixed_word = collocation.get('fixed_word')  # phrase
        gy_paraphrase = collocation.get('gy_paraphrase')
        for paraphrase in gy_paraphrase:
            frequency_name = paraphrase.get('frequency_name')  # frequency_name
            chinese = paraphrase.get('chinese')  # chinese
            gy_example: List = paraphrase.get('gy_example')  # list 类型
            gy_notes: List = paraphrase.get('gy_notes')

            dic = {
                "phrase": fixed_word,
                "frequency_name": frequency_name,
                "chinese": chinese,
            }

            items = []

            for (gy_ex, note) in zip_longest(gy_example, gy_notes):  # 这里存在对应关系，但似乎对应那个都可以
                new_dic = copy.deepcopy(dic)
                if gy_ex:
                    example = gy_ex.get('english') + '[E{0}-{1}]'.format(len(gy_example), gy_example.index(gy_ex) + 1)
                    translation = gy_ex.get('chinese')
                else:
                    example = ''
                    translation = ''
                if note:
                    notes = note.get('notes')
                else:
                    notes = ''
                new_dic.update({"example": example, "translation": translation, "notes": notes})
                items.append(new_dic)
            collocation_data.extend(items)
    return collocation_data


def parse_xlsx():
    df = pd.read_excel(r'./split_table_data/word.xlsx', sheet_name='Sheet1', header=None)  # sheet_name 读取那个工作表
    data = df.values  # 全部数据
    header = data[0]  # 读取第一行的列名
    other_lines = data[1:]  # 除第一行外剩余数据
    result = [dict(zip(header, line)) for line in other_lines]
    xlsx_data = []  # 所有解析数据
    for i in result:
        word_id = i.get('id')

        detail_json = i.get('detail_json')
        if not isinstance(detail_json, float):  # 存在多条无内容数据 如:china
            json_data = json.loads(i.get('detail_json'))
            gy_fixed_collocation = json_data.get('gy_fixed_collocation')
            collocation_data = parse_collocation(gy_fixed_collocation)
            for coll in collocation_data:
                phrase = coll.get('phrase')
                frequency_name = coll.get('frequency_name')
                chinese = coll.get('chinese')
                example = coll.get('example')
                translation = coll.get('translation')
                notes = coll.get('notes')
                xlsx_data.append([
                    word_id, phrase, frequency_name, chinese, example, translation, notes
                ])
    write_data_to_xlsx(xlsx_data, word_header, 'parse_collocation')


if __name__ == '__main__':
    parse_xlsx()
